package br.com.localfloripa.dao;

import java.util.Arrays;
import java.util.List;

import javax.inject.Inject;
import javax.inject.Named;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import br.com.localfloripa.entities.Anuncio;
import br.com.localfloripa.entities.Cliente;
import br.com.localfloripa.entities.Estado;
import br.com.localfloripa.entities.Imovel;
import br.com.localfloripa.entities.Municipio;
import br.com.localfloripa.entities.Ramo;
import br.com.localfloripa.entities.Servico;
import br.com.localfloripa.entities.TipoAvaliacao;
import br.com.localfloripa.entities.TipoNegociacao;
import br.com.localfloripa.entities.Usuario;

@Named("anuncioDao")
public class AnuncioDao extends DaoManager<Anuncio> {
	
	private static final Logger log = LoggerFactory.getLogger(AnuncioDao.class);
	
	@Inject
	@Named("representanteDao")
	private RepresentanteDao representanteDao;
	
	public Anuncio findById(Long idAnuncio) throws Exception {
		return em.find(Anuncio.class, idAnuncio);
	}
	
	public List<Anuncio> listAnunciosPaginada(int pagina, int max) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a ORDER BY a.empresa.pessoaJuridica.nome");
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		return query.getResultList();
	}
	
	public List<Anuncio> listAnunciosByLoggedUserPaginada(int pagina,int max) throws Exception {
		
		Usuario loggedUser = Usuario.getLoggedUser();
		Long idUsuario = null;
		String jpql = "";
		if (loggedUser.hasRole("CORRETOR")) {
			jpql = "SELECT a FROM  Anuncio a WHERE a.representante.id = :idUsuario ORDER BY a.empresa.pessoaJuridica.nome";
			idUsuario = loggedUser.getCliente().getId();
		} else {
			jpql = "SELECT a FROM  Anuncio a WHERE a.cliente.usuario.id = :idUsuario ORDER BY a.empresa.pessoaJuridica.nome";
			idUsuario = loggedUser.getId();
		}
		Query query = em.createQuery(jpql);
		query.setParameter("idUsuario",idUsuario);
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		return query.getResultList();		
	}
	
	public Long countListAnuncios() {
		Query query = em.createQuery("SELECT COUNT(a.id) FROM Anuncio a");		
		return (Long) query.getSingleResult();
	}
	
	public Long countListAnunciosByLoggedUser() {
		Usuario loggedUser = Usuario.getLoggedUser();
		Long idUsuario = null;
		String jpql = "";
		if (loggedUser.hasRole("CORRETOR")) {
			jpql = "SELECT COUNT(a.id) FROM Anuncio a WHERE a.representante.id = :idUsuario";
			idUsuario = loggedUser.getCliente().getId();
		} else {
			jpql = "SELECT COUNT(a.id) FROM Anuncio a WHERE a.cliente.usuario.id = :idUsuario";
			idUsuario = loggedUser.getId();
		}
		Query query = em.createQuery(jpql);
		query.setParameter("idUsuario", idUsuario);
		return (Long) query.getSingleResult();
	}
	
	public List<Imovel> searchImoveisPaginada(Cliente cliente,String tipoNegociacao,Municipio municipio,String[] bairros,String dormitorios,String garagens,String banheiros,String suites,Boolean album,int pagina,int max) throws Exception {
		
		String jpql = "SELECT i FROM Imovel i LEFT JOIN FETCH i.avaliacoes " +				
				"WHERE i.tipoNegociacao = :tipoNegociacao " +
				"AND i.empresa.endereco.municipio.id = :idMunicipio ";
		
		if (representanteDao.findById(cliente.getId()) != null) { //corretor
			jpql += "AND i.representante.id = :idCliente ";
		} else {
			jpql += "AND i.cliente.id = :idCliente ";
		}
				
		if (album != null) {
			if (album) {
				jpql += "AND i.album IS NOT EMPTY ";
			} else {
				jpql += "AND i.album IS EMPTY ";
			}					
		}					
		if (dormitorios != null && !dormitorios.equals("")) {
			if (Integer.parseInt(dormitorios) > 0 && Integer.parseInt(dormitorios) < 5) {
				jpql += "AND i.dormitorios = :dormitorios "; 
			} else if (Integer.parseInt(dormitorios) >= 5) {
				jpql += "AND i.dormitorios >= :dormitorios ";
			}
		}
		if (garagens != null && !garagens.equals("")) {
			if (Integer.parseInt(garagens) > 0)
				jpql += "AND i.garagens = :garagens ";
		}
		if (banheiros != null && !banheiros.equals("")) {
			if (Integer.parseInt(banheiros) > 0)
				jpql += "AND i.banheiros = :banheiros ";
		}
		if (suites != null && !suites.equals("")) {
			if (Integer.parseInt(suites) > 0)
				jpql += "AND i.suites = :suites ";
		}
		if (bairros != null) {
			if (bairros.length > 0)
				jpql += "AND UPPER(i.empresa.endereco.bairro) IN (:bairros) ";
		}
		jpql +=	"AND i.ativo IS TRUE " +
				"AND i.cliente.ativo IS TRUE " +
				"AND i.ramo.id = 4 " +
				"ORDER BY i.empresa.pessoaJuridica.nome";		
		Query query = em.createQuery(jpql);
		query.setParameter("idCliente", cliente.getId());
		query.setParameter("tipoNegociacao", TipoNegociacao.fromName(tipoNegociacao));
		query.setParameter("idMunicipio", municipio.getId());		
		if (dormitorios != null && !dormitorios.equals("")) {
			if (Integer.parseInt(dormitorios) > 0)
				query.setParameter("dormitorios", Integer.parseInt(dormitorios));
		}
		if (garagens != null && !garagens.equals("")) {
			if (Integer.parseInt(garagens) > 0)
				query.setParameter("garagens", Integer.parseInt(garagens));
		}
		if (banheiros != null && !banheiros.equals("")) {
			if (Integer.parseInt(banheiros) > 0)
				query.setParameter("banheiros", Integer.parseInt(banheiros));
		}
		if (suites != null && !suites.equals("")) {
			if (Integer.parseInt(suites) > 0)
				query.setParameter("suites", Integer.parseInt(suites));
		}
		if (bairros != null) {
			if (bairros.length > 0)
				query.setParameter("bairros", Arrays.asList(bairros));
		}
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Imovel> searchAnunciosPaginadaByCliente(Cliente cliente,int pagina,int max) throws Exception {
		
		String jpql = null;
		if (representanteDao.findById(cliente.getId()) != null) { // corretor			
			jpql = "SELECT i FROM Imovel i LEFT JOIN FETCH i.avaliacoes WHERE i.representante.id = :id AND i.ativo IS TRUE AND i.cliente.ativo IS TRUE AND i.ramo.id = 4 ORDER BY i.empresa.pessoaJuridica.nome";
		} else {
			jpql = "SELECT i FROM Imovel i LEFT JOIN FETCH i.avaliacoes WHERE i.cliente.id = :id AND i.ativo IS TRUE AND i.cliente.ativo IS TRUE AND i.ramo.id = 4 ORDER BY i.empresa.pessoaJuridica.nome";
		}
		Query query = em.createQuery(jpql);
		query.setParameter("id", cliente.getId());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Anuncio> searchAnunciosPaginadaByRamo(int pagina,int max,Ramo ramo,Municipio municipio) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes WHERE a.ramo.id = :idRamo AND a.empresa.endereco.municipio.id = :idMunicipio AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE ORDER BY a.empresa.pessoaJuridica.nome");
		query.setParameter("idRamo", ramo.getId());
		query.setParameter("idMunicipio", municipio.getId());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Anuncio> searchAnunciosPaginadaByServico(int pagina,int max,Servico servico,Estado estado) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes WHERE a.servico.id = :idServico and a.empresa.endereco.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE ORDER BY a.empresa.pessoaJuridica.nome");
		query.setParameter("idServico", servico.getId());
		query.setParameter("idEstado", estado.getId());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Anuncio> searchAnunciosPaginadaByMunicipio(int pagina,int max,Municipio municipio) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes  WHERE a.empresa.endereco.municipio.estado.id = :idEstado and a.empresa.endereco.municipio.id = :idMunicipio AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE ORDER BY a.empresa.pessoaJuridica.nome");
		query.setParameter("idEstado", municipio.getEstado().getId());
		query.setParameter("idMunicipio", municipio.getId());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Anuncio> searchAnunciosPaginadaByBairro(int pagina,int max,String bairro,Estado estado) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes WHERE UPPER(TRIM(a.empresa.endereco.bairro)) = UPPER(TRIM(:bairro)) AND a.empresa.endereco.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE ORDER BY a.empresa.pessoaJuridica.nome");
		query.setParameter("bairro", bairro);
		query.setParameter("idEstado", estado.getId());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Anuncio> searchAnunciosPaginadaByEstado(int pagina,int max,Estado estado) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes WHERE a.empresa.endereco.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE ORDER BY a.empresa.pessoaJuridica.nome");
		query.setParameter("idEstado", estado.getId());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Anuncio> searchAnunciosPaginada(int pagina,int max,String searchTerm) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes " +
				"WHERE (UPPER(a.empresa.pessoaJuridica.nome) LIKE :searchTerm " +
				"OR UPPER(a.servico.nome) LIKE :searchTerm " +
				"OR UPPER(a.ramo.nome) LIKE :searchTerm " +
				"OR UPPER(a.empresa.endereco.municipio.nome) LIKE :searchTerm " +
				"OR UPPER(a.descricao) LIKE :searchTerm " +
				"OR UPPER(a.empresa.telefoneFixo) LIKE :searchTerm " +
				"OR UPPER(a.empresa.telefoneCelular) LIKE :searchTerm " +
				"OR UPPER(a.empresa.endereco.edificio) LIKE :searchTerm) " +
				"AND a.ativo IS TRUE " +
				"AND a.cliente.ativo IS TRUE " +
				"ORDER BY a.empresa.pessoaJuridica.nome");
		query.setParameter("searchTerm", "%"+searchTerm.toUpperCase()+"%");
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public List<Anuncio> searchAnunciosByLoggedUserPaginada(String searchTerm,int pagina,int max) throws Exception {
		
		Usuario loggedUser = Usuario.getLoggedUser();
		Long idUsuario = null;
		String jpql = "";
		if (loggedUser.hasRole("CORRETOR")) {
			jpql = "SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes " +
					"WHERE (UPPER(a.empresa.pessoaJuridica.nome) LIKE :searchTerm " +
					"OR UPPER(a.servico.nome) LIKE :searchTerm " +
					"OR UPPER(a.ramo.nome) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.municipio.nome) LIKE :searchTerm " +
					"OR UPPER(a.descricao) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneFixo) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneCelular) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.edificio) LIKE :searchTerm) " +
					"AND a.representante.id = :idUsuario " +
					"ORDER BY a.empresa.pessoaJuridica.nome";
			idUsuario = Usuario.getLoggedUser().getCliente().getId();
		} else {
			jpql = "SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes " +
					"WHERE (UPPER(a.empresa.pessoaJuridica.nome) LIKE :searchTerm " +
					"OR UPPER(a.servico.nome) LIKE :searchTerm " +
					"OR UPPER(a.ramo.nome) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.municipio.nome) LIKE :searchTerm " +
					"OR UPPER(a.descricao) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneFixo) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneCelular) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.edificio) LIKE :searchTerm) " +
					"AND a.cliente.usuario.id = :idUsuario " +
					"ORDER BY a.empresa.pessoaJuridica.nome";
			idUsuario = Usuario.getLoggedUser().getId();
		}		
		Query query = em.createQuery(jpql);
		query.setParameter("searchTerm", "%"+searchTerm.toUpperCase()+"%");
		query.setParameter("idUsuario", idUsuario);
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public Long countAnuncios(String searchTerm) {	
		
		Query query = em.createQuery("SELECT COUNT(a.id) FROM Anuncio a WHERE (UPPER(a.empresa.pessoaJuridica.nome) LIKE :searchTerm " +
				"OR UPPER(a.servico.nome) LIKE :searchTerm " +
				"OR UPPER(a.ramo.nome) LIKE :searchTerm " +
				"OR UPPER(a.empresa.endereco.municipio.nome) LIKE :searchTerm " +
				"OR UPPER(a.descricao) LIKE :searchTerm " +
				"OR UPPER(a.empresa.telefoneFixo) LIKE :searchTerm " +
				"OR UPPER(a.empresa.telefoneCelular) LIKE :searchTerm " +
				"OR UPPER(a.empresa.endereco.edificio) LIKE :searchTerm) " +
				"AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE");
		query.setParameter("searchTerm", "%"+searchTerm.toUpperCase()+"%");
				
		return (Long) query.getSingleResult();
	}
	
	public Long countAnunciosByLoggedUser(String searchTerm) {
		
		
		Usuario loggedUser = Usuario.getLoggedUser();
		Long idUsuario = null;
		String jpql = "";
		if (loggedUser.hasRole("CORRETOR")) {
			jpql = "SELECT COUNT(a.id) FROM Anuncio a " +
					"WHERE (UPPER(a.empresa.pessoaJuridica.nome) LIKE :searchTerm " +
					"OR UPPER(a.servico.nome) LIKE :searchTerm " +
					"OR UPPER(a.ramo.nome) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.municipio.nome) LIKE :searchTerm " +
					"OR UPPER(a.descricao) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneFixo) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneCelular) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.edificio) LIKE :searchTerm " +
					"OR a.id = :searchTermId) " +
					"AND a.representante.id = :idUsuario";	
			idUsuario = Usuario.getLoggedUser().getCliente().getId();
		} else {
			jpql = "SELECT COUNT(a.id) FROM Anuncio a " +
					"WHERE (UPPER(a.empresa.pessoaJuridica.nome) LIKE :searchTerm " +
					"OR UPPER(a.servico.nome) LIKE :searchTerm " +
					"OR UPPER(a.ramo.nome) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.municipio.nome) LIKE :searchTerm " +
					"OR UPPER(a.descricao) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneFixo) LIKE :searchTerm " +
					"OR UPPER(a.empresa.telefoneCelular) LIKE :searchTerm " +
					"OR UPPER(a.empresa.endereco.edificio) LIKE :searchTerm " +
					"OR a.id = :searchTermId) " +
					"AND a.cliente.usuario.id = :idUsuario";
			idUsuario = Usuario.getLoggedUser().getId();
		}		
		Query query = em.createQuery(jpql);
		query.setParameter("searchTerm", "%"+searchTerm.toUpperCase()+"%");
		query.setParameter("searchTermId", Long.parseLong(searchTerm));
		query.setParameter("idUsuario", idUsuario);
				
		return (Long) query.getSingleResult();
		
	}
	
	public Long countImoveis(Cliente cliente,String tipoNegociacao,Municipio municipio,String[] bairros,String dormitorios,String garagens,String banheiros,String suites,Boolean album) throws Exception {
		
		
		String jpql = "SELECT COUNT(i.id) FROM Imovel i " +
				"WHERE i.tipoNegociacao = :tipoNegociacao " +
				"AND i.empresa.endereco.municipio.id = :idMunicipio ";
		
		if (representanteDao.findById(cliente.getId()) != null) { //corretor
			jpql += "AND i.representante.id = :idCliente ";
		} else {
			jpql += "AND i.cliente.id = :idCliente ";
		}
		
		if (album != null) {
			if (album) {
				jpql += "AND i.album IS NOT EMPTY ";
			} else {
				jpql += "AND i.album IS EMPTY ";
			}					
		}	
		if (dormitorios != null && !dormitorios.equals("")) {
			if (Integer.parseInt(dormitorios) > 0)
				jpql += "AND i.dormitorios = :dormitorios "; 
		}
		if (garagens != null && !garagens.equals("")) {
			if (Integer.parseInt(garagens) > 0)
				jpql += "AND i.garagens = :garagens ";
		}
		if (banheiros != null && !banheiros.equals("")) {
			if (Integer.parseInt(banheiros) > 0)
				jpql += "AND i.banheiros = :banheiros ";
		}
		if (suites != null && !suites.equals("")) {
			if (Integer.parseInt(suites) > 0)
				jpql += "AND i.suites = :suites ";
		}
		if (bairros != null) {
			if (bairros.length > 0)
				jpql += "AND UPPER(i.empresa.endereco.bairro) IN (:bairros) ";
		}
		jpql += "AND i.ativo IS TRUE " +
				"AND i.ramo.id = 4 " +
				"AND i.cliente.ativo IS TRUE";		
		Query query = em.createQuery(jpql);
		query.setParameter("idCliente", cliente.getId());
		query.setParameter("tipoNegociacao", TipoNegociacao.fromName(tipoNegociacao));
		query.setParameter("idMunicipio", municipio.getId());
		if (dormitorios != null && !dormitorios.equals("")) {
			if (Integer.parseInt(dormitorios) > 0)
				query.setParameter("dormitorios", Integer.parseInt(dormitorios));
		}
		if (garagens != null && !garagens.equals("")) {
			if (Integer.parseInt(garagens) > 0)
				query.setParameter("garagens", Integer.parseInt(garagens));
		}
		if (banheiros != null && !banheiros.equals("")) {
			if (Integer.parseInt(banheiros) > 0)
				query.setParameter("banheiros", Integer.parseInt(banheiros));
		}
		if (suites != null && !suites.equals("")) {
			if (Integer.parseInt(suites) > 0)
				query.setParameter("suites", Integer.parseInt(suites));
		}
		if (bairros != null) {
			if (bairros.length > 0)
				query.setParameter("bairros", Arrays.asList(bairros));
		}
		return (Long) query.getSingleResult();
	}
	
	public Long countAnunciosByCliente(Cliente cliente) {
		
		String jpql = null;
		try {
			if (representanteDao.findById(cliente.getId()) != null) { // corretor
				jpql = "SELECT COUNT(i.id) FROM Imovel i WHERE i.representante.id = :id AND i.ativo IS TRUE AND i.cliente.ativo IS TRUE AND i.ramo.id = 4";
			} else {
				jpql = "SELECT COUNT(i.id) FROM Imovel i WHERE i.cliente.id = :id AND i.ativo IS TRUE AND i.cliente.ativo IS TRUE AND i.ramo.id = 4";
			}
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return 0L;
		}		
		Query query = em.createQuery(jpql);
		query.setParameter("id", cliente.getId());
				
		return (Long) query.getSingleResult();
	}
	
	public Long countAnunciosByRamo(Ramo ramo,Municipio municipio) {	
		
		Query query = em.createQuery("SELECT COUNT(a.id) FROM Anuncio a WHERE a.ramo.id = :idRamo AND a.empresa.endereco.municipio.id = :idMunicipio AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE");
		query.setParameter("idRamo", ramo.getId());
		query.setParameter("idMunicipio", municipio.getId());
				
		return (Long) query.getSingleResult();
	}
	
	public Long countAnunciosByServico(Servico servico,Estado estado) {	
		
		Query query = em.createQuery("SELECT COUNT(a.id) FROM Anuncio a WHERE a.servico.id = :idServico AND a.empresa.endereco.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE");
		query.setParameter("idServico", servico.getId());
		query.setParameter("idEstado", estado.getId());
				
		return (Long) query.getSingleResult();
	}
	
	public Long countAnunciosByMunicpio(Municipio municipio) {	
		
		Query query = em.createQuery("SELECT COUNT(a.id) FROM Anuncio a WHERE a.empresa.endereco.municipio.estado.id = :idEstado and a.empresa.endereco.municipio.id = :idMunicipio AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE");
		query.setParameter("idEstado", municipio.getEstado().getId());
		query.setParameter("idMunicipio", municipio.getId());
				
		return (Long) query.getSingleResult();
	}
	
	public Long countAnunciosByBairro(String bairro,Estado estado) {	
		
		Query query = em.createQuery("SELECT COUNT(a.id) FROM Anuncio a WHERE UPPER(TRIM(a.empresa.endereco.bairro)) = UPPER(TRIM(:bairro)) AND a.empresa.endereco.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE");
		query.setParameter("bairro", bairro);
		query.setParameter("idEstado", estado.getId());
				
		return (Long) query.getSingleResult();
	}
	
	public Long countAnunciosByEstado(Estado estado) {	
		
		Query query = em.createQuery("SELECT COUNT(a.id) FROM Anuncio a WHERE a.empresa.endereco.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE");
		query.setParameter("idEstado", estado.getId());
				
		return (Long) query.getSingleResult();
	}
	
	public Double calculaReputacao(Anuncio anuncio,TipoAvaliacao tipo) throws Exception {
		
		Query query = em.createQuery("SELECT ROUND(AVG(av.classificacao)) FROM Anuncio a JOIN a.avaliacoes as av WHERE a.id = :idAnuncio AND av.tipo = :tipo AND a.ativo IS TRUE AND a.cliente.ativo IS TRUE");
		query.setParameter("idAnuncio", anuncio.getId());
		query.setParameter("tipo", tipo);
		
		return (Double) query.getSingleResult();
		
	}
	
	public Anuncio forceEagerLoad(Long idAnuncio) throws Exception {
		
		Query query = em.createQuery("SELECT a FROM Anuncio a LEFT JOIN FETCH a.avaliacoes LEFT JOIN FETCH a.mensagens WHERE a.id = :idAnuncio");
		query.setParameter("idAnuncio", idAnuncio);
		
		return (Anuncio) query.getSingleResult();
		
	}
	
	public Boolean verificaLancamentos(Cliente cliente) throws Exception {
		
		Query query = em.createQuery("SELECT COUNT(a.lancamento) FROM Anuncio a WHERE a.lancamento IS TRUE AND a.cliente.id = :idCliente AND a.ativo IS TRUE");
		query.setParameter("idCliente", cliente.getId());
		Long lancamentos = (Long) query.getSingleResult();  		
		
		return lancamentos != null && lancamentos > 0 ? true : false;		
	}
	
	public List<Imovel> listTopLancamentos(Cliente cliente) throws Exception {
		
		Query query = em.createQuery("SELECT i FROM Imovel i WHERE i.lancamento IS TRUE AND i.ativo IS TRUE AND i.cliente.id = :idCliente ORDER BY RANDOM()");
		query.setParameter("idCliente", cliente.getId());
		query.setMaxResults(2);
		
		return query.getResultList();		
	}
	
	public List<Imovel> listLancamentos(Cliente cliente,int pagina,int max) throws Exception {
		
		Query query = em.createQuery("SELECT i FROM Imovel i WHERE i.lancamento IS TRUE AND i.ativo IS TRUE AND i.cliente.id = :idCliente ORDER BY i.id");
		query.setParameter("idCliente", cliente.getId());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public Long countLancamentos(Cliente cliente) {
		
		Query query = em.createQuery("SELECT COUNT(i) FROM Imovel i WHERE i.lancamento IS TRUE AND i.ativo IS TRUE AND i.cliente.id = :idCliente");
		query.setParameter("idCliente", cliente.getId());
		
		return (Long) query.getSingleResult();
	}
	
	public Imovel getDestaque(Cliente cliente) throws Exception {
		
		Query query = em.createQuery("SELECT i FROM Imovel i WHERE i.ativo IS TRUE AND i.cliente.id = :idCliente ORDER BY RANDOM()");
		query.setParameter("idCliente", cliente.getId());
		query.setMaxResults(1);
		
		return (Imovel) query.getSingleResult();
	}
	
	public List<Imovel> listImoveisByTipoNegociacao(Cliente cliente,TipoNegociacao tipoNegociacao,int pagina,int max) throws Exception {
		
		Query query = em.createQuery("SELECT i FROM Imovel i WHERE i.ativo IS TRUE AND i.cliente.id = :idCliente AND i.tipoNegociacao = :tipoNegociacao ORDER BY i.id");
		query.setParameter("idCliente",cliente.getId());
		query.setParameter("tipoNegociacao", tipoNegociacao);
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		return query.getResultList();
	}
	
	public Long countImoveisByTipoNegociacao(Cliente cliente,TipoNegociacao tipoNegociacao) {
		
		Query query = em.createQuery("SELECT COUNT(i) FROM Imovel i WHERE i.ativo IS TRUE AND i.cliente.id = :idCliente AND i.tipoNegociacao = :tipoNegociacao");
		query.setParameter("idCliente",cliente.getId());
		query.setParameter("tipoNegociacao", tipoNegociacao);
		
		return (Long) query.getSingleResult();
	}
	
}
